package com.csmy.my.center.util.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.csmy.my.center.util.CTConstants;
import com.csmy.my.center.util.PageModel;
import com.csmy.my.center.util.StringUtil;
import com.csmy.my.center.util.base.Channel;
import com.csmy.my.center.util.dataconvert.Dto;
import com.csmy.my.center.util.dataconvert.impl.BaseDto;

public class DBManager {

	public static int start = 0;
	public static Integer pageNo = 1;
	public static int pageSize = 20;

	/**
	 * 执行非查询的sql语句，比如创建表，加载数据等等
	 * @param sql
	 * @return
	 */
	public boolean execute(String sql) {
		boolean rel = true;
		Statement sta = null;
		Connection con = null;
		try {
			con = DBConnect.getMysqlConnect();
			sta = con.createStatement();
			sta.executeUpdate(sql);
		} catch (SQLException e) {
			rel = false;
			e.printStackTrace();
			StringUtil.xprint("jdbc非查询方法报错"); // 异常描述
		} finally {
			OperateDB.close(con, null, sta);
		}
		return rel;
	}

	/**
	 * 使用Statement查询数据，返回ResultSet
	 * @param sql
	 * @return
	 */
	public ResultSet query(String sql) {
		ResultSet rs = null;
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = DBConnect.getMysqlConnect();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			StringUtil.xprint("jdbc查询方法报错"); // 异常描述
		}
		return rs;
	}

	/**
	 * 获取列表总数
	 * @param sql
	 * @return
	 */
	public int getTotalCount(String sql) {
		int totalCount = 0;
		try {

			List<Dto> cList = queryForList(sql);
			if (!StringUtil.checkListBlank(cList)) {
				totalCount = cList.size();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return totalCount;
	}

	/**
	 * 转换分页参数
	 * @param dto
	 * @return
	 */
	public Dto getPageDto(Dto dto) {
		/**********************分页开始*************************/
		Integer offset = dto.getAsInteger("pager.offset");
		if (offset != null && offset != 0) {
			pageNo = offset / pageSize;
			start = (pageNo * pageSize);
		} else {
			start = 0;
		}
		dto.put("start", start);
		dto.put("limit", pageSize);
		/**********************分页结束*************************/
		return dto;
	}

	/**
	 * 获取分页对象
	 * @param dto
	 * @return
	 */
	public PageModel getPageModel(List<Dto> dList, int start,
			int totalCount) {
		PageModel pager = new PageModel();
		pager.setDatas(dList);
		pager.setPageNo((start / CTConstants.PAGER_SIZE) + 1);
		pager.setPageSize(CTConstants.PAGER_SIZE);
		pager.setCount(totalCount);
		if (!StringUtil.checkListBlank(dList)) {
			System.out.println("信息集合大小:" + dList.size());
		}
		return pager;
	}
	
	/**
	 * 获取分页对象
	 * @param dto
	 * @return
	 */
	public PageModel getPageModel(List<Dto> dList, int start,int pageSize,
			int totalCount) {
		PageModel pager = new PageModel();
		pager.setDatas(dList);
		pager.setPageNo((start / pageSize) + 1);
		pager.setPageSize(pageSize);
		pager.setCount(totalCount);
		if (!StringUtil.checkListBlank(dList)) {
			System.out.println("信息集合大小:" + dList.size());
		}
		return pager;
	}

	/**
	 * 使用PreparedStatement查询数据，返回ResultSet
	 * @param sql
	 * @param values
	 * @return
	 */
	public ResultSet query(String sql, String[] values) {
		ResultSet rs = null;
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = DBConnect.getMysqlConnect();
			pst = con.prepareStatement(sql);
			setValue(pst, values);
			rs = pst.executeQuery();
		} catch (SQLException e) {
			StringUtil.xprint("jdbc查询方法报错"); // 异常描述
			e.printStackTrace();
		} finally {
			OperateDB.close(con, rs, pst);
		}
		return rs;
	}
	
	
	/**
	 * 使用PreparedStatement更新数据，返回条数
	 * @param sql
	 * @param values
	 * @return
	 */
	public int updateBySQL(String sql, String[] values) {
		int rst = 0;
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = DBConnect.getMysqlConnect();
			pst = con.prepareStatement(sql);
			setValue(pst, values);
			rst = pst.executeUpdate();
		} catch (SQLException e) {
			StringUtil.xprint("jdbc方法报错"); // 异常描述
			e.printStackTrace();
		} finally {
			OperateDB.close(con, null, pst);
		}
		return rst;
	}

	/**
	 * 使用Statement查询数据，返回List集合，数据量比较小的时候用
	 * @param sql
	 * @return
	 */
	public List<Dto> queryForList(String sql) {
		ResultSet rs = null;
		Statement sta = null;
		Connection con = null;
		List<Dto> dtoList = null;
		try {
			con = DBConnect.getMysqlConnect();
			sta = con.createStatement();
			rs = sta.executeQuery(sql);
			Dto mapDto = null;
			ResultSetMetaData rsmd = rs.getMetaData();
			int rowCnt = rsmd.getColumnCount();
			dtoList = new ArrayList<Dto>();
			while (rs.next()) {
				mapDto = new BaseDto();
				for (int i = 1; i <= rowCnt; i++) {
					mapDto.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				dtoList.add(mapDto);
			}
		} catch (SQLException e) {
			StringUtil.xprint("jdbc查询数据返回list方法报错"); // 异常描述
			e.printStackTrace(); // 输出堆栈信息
		} finally {
			OperateDB.close(con, rs, sta);
		}
		return dtoList;
	}

	/**
	 * 使用Statement查询数据，返回List集合，数据量比较小的时候用
	 * @param sql
	 * @return
	 */
	public List<Channel> queryList(String sql) {
		ResultSet rs = null;
		Statement sta = null;
		Connection con = null;
		List<Channel> dtoList = null;
		try {
			con = DBConnect.getMysqlConnect();
			sta = con.createStatement();
			rs = sta.executeQuery(sql);
			dtoList = new ArrayList<Channel>();
			while (rs.next()) {
				Channel channel = new Channel();
				channel.channel_id = rs.getString("channel_id");
				channel.channel_name = rs.getString("channel_name");
				channel.channel_txt = rs.getString("channel_txt");
				dtoList.add(channel);
			}
		} catch (SQLException e) {
			StringUtil.xprint("jdbc查询数据返回list方法报错"); // 异常描述
			e.printStackTrace(); // 输出堆栈信息
		} finally {
			OperateDB.close(con, rs, sta);
		}
		return dtoList;
	}

	private void setValue(PreparedStatement pst, String[] values)
			throws SQLException {
		try {
			for (int i = 0; i < values.length; i++) {
				pst.setString(i + 1, values[i]);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Exception err;
	/**
	 * 通用jdbc保存数据和操作
	 * @param sql
	 * @return
	 */
	public boolean insertBySQL(String sql) {
		Statement sta = null;
		Connection con = null;
		boolean bool = false;
		try {

			con = DBConnect.getMysqlConnect();
			sta = con.createStatement();
			int temp = sta.executeUpdate(sql);
			if (temp > 0) {
				bool = true;
				System.out.println("语句: " + sql + " 执行成功，影响了" + temp + "行数据");
			} else {
				bool = false;
				System.out.println("语句: " + sql + " 执行失败");
			}

		} catch (Exception e) {
			System.out.println("语句: " + sql + " 执行失败");
			e.printStackTrace();
			err=e;
		} finally {
			OperateDB.close(con, null, sta);
		}

		return bool;
	}

	/**
	 * 通用jdbc保存数据和操作
	 * @param sql
	 * @return
	 */
	public int addDataBySQL(String sql) {
		int bkey = 0;
		ResultSet rs = null;
		Statement sta = null;
		Connection con = null;
		try {

			con = DBConnect.getMysqlConnect();
			sta = con.createStatement();
			int row = sta.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			rs = sta.getGeneratedKeys();
			if (rs.next()) {
				bkey = rs.getInt(row);
				System.out.println("当前ID=" + bkey);
			}

		} catch (Exception e) {
			System.out.println("语句: " + sql + " 执行失败");
			e.printStackTrace();
		} finally {
			OperateDB.close(con, rs, sta);
		}

		return bkey;
	}

}
